package net.rrkk.photoman.repository;

import net.rrkk.photoman.entity.PhotoCategory;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface PhotoCategoryRepository extends JpaRepository<PhotoCategory, Long> {
    
    // 查找顶级分类（parent_id为null的分类）
    List<PhotoCategory> findByParentIdIsNull();
    
    // 查找指定父分类下的所有子分类
    List<PhotoCategory> findByParentId(Long parentId);
    
    // 查找指定层级的所有分类
    List<PhotoCategory> findByLevel(Integer level);
    
    // 根据名称查找分类
    List<PhotoCategory> findByNameContaining(String name);
    
    // 查找启用的分类
    List<PhotoCategory> findByIsActiveTrue();
    
    // 查找启用的顶级分类
    List<PhotoCategory> findByParentIdIsNullAndIsActiveTrue();
    
    // 查找启用的指定父分类下的所有子分类
    List<PhotoCategory> findByParentIdAndIsActiveTrue(Long parentId);
    
    // 使用原生SQL查询获取分类树结构
    @Query(value = "WITH RECURSIVE category_path AS (" +
            "SELECT id, name, parent_id, level, sort_order, is_active, created_at, updated_at, ARRAY[id] AS path " +
            "FROM photo_category " +
            "WHERE parent_id IS NULL " +
            "UNION ALL " +
            "SELECT c.id, c.name, c.parent_id, c.level, c.sort_order, c.is_active, c.created_at, c.updated_at, cp.path || c.id " +
            "FROM photo_category c " +
            "JOIN category_path cp ON c.parent_id = cp.id" +
            ") " +
            "SELECT * FROM category_path ORDER BY path", nativeQuery = true)
    List<Object[]> findCategoryTree();
}